'''
Created on 2020年3月9日

@author: yinfs
'''
from dao import dbaccess

class PcsDao(object):
    '''
    classdocs
    '''


    def __init__(self):
        '''
        Constructor
        '''
    
    def create_tab_photoclass(self):
        '''
        创建photo_class 表
        '''
        db = dbaccess.DBAccess()
        sql = '''
        create table pcs_photo_class(
            id integer primary key autoincrement,
            dirs_id text,
            job_id text,
            source_name text,
            file_size integer,
            file_extension text,
            file_md5 text,
            new_filename text,
            image_datetime timestamp,
            image_mark text,
            image_model text,
            image_length integer,
            image_width integer,
            create_datetime timestamp default (datetime('now','localtime')))
        '''
        C1 = db.execute_create(sql)
        if C1:
            print('创建 photo_class 存储成功！')
            return True
        else:
            print('创建  photo_class 存储 失败')
            return False
        
        db.close()  
          
    def create_tab_pcs_directory(self):
        '''
        创建photo_directory 表
        '''
        db = dbaccess.DBAccess()
        sql = '''
        create table pcs_directory(
            id text,
            job_id text,
            source_directory text,
            source_directory_md5 text,
            directory_num integer,
            file_num integer,
            file_extension_num integer,
            file_extension_size integer,
            file_repeat_size integer,
            repeat_percent real default 0,
            deleted real default 0,
            create_datetime timestamp default (datetime('now','localtime')))
        '''
        C1 = db.execute_create(sql)
        if C1:
            print('创建pcs_directory 存储成功！')
            return True
        else:
            print('创建 pcs_directory 存储失败')
            return False
        
        db.close()
        
    def create_tab_pcs_job(self):
        '''
        创建photo_directory 表
        '''
        db = dbaccess.DBAccess()
        sql = '''
        create table pcs_job(
            id text,
            source_directory text,
            source_directory_md5 text,
            create_datetime timestamp default (datetime('now','localtime')))
        '''
        C1 = db.execute_create(sql)
        if C1:
            print('创建  pcs_job 存储成功！')
            return True
        else:
            print('创建  pcs_job 存储失败')
            return False
        
        db.close()
        
    def if_tabe(self,tabname):
        '''
        判断数据库中 指定的表是否存在，存在反馈=1, 不存在 反馈=0
        :return flag
        '''
        db = dbaccess.DBAccess()
        try:
            query_sql = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='"+tabname+"'"
            ob = []
            res = db.execute_query(query_sql,ob)
            result =res.fetchall()
            count = result[0][0]            
        except Exception as e:
            print("查询数据库中 指定的表是否存在错误:",e)
        db.close() 
        return count
    
    def insert_photo_class(self,photoList):
        '''
        向photo_class数据库插入数据。
        '''
        db = dbaccess.DBAccess()
        try:
            sql = 'insert into pcs_photo_class (job_id,dirs_id,source_name,file_size,file_extension,file_md5,new_filename,image_datetime,image_mark,image_model,image_length,image_width) values (?,?,?,?,?,?,?,?,?,?,?,?)'
            ins_r=db.execute_update(sql, photoList)
            if ins_r:
                print('插入 photo_class 表数据成功:')
            else:
                print('插入 photo_class 表 数据失败')
        except Exception as e:
            print('录入photo_class 数据库数据失败：',e)
        db.close() 
    
    def insert_pcs_directory(self,dirs_list):
        '''
        向photo_directory数据库插入数据。
        '''
        db = dbaccess.DBAccess()
        try:
            sql = 'insert into pcs_directory (directory_num,file_num,file_extension_num,file_extension_size,id,source_directory,source_directory_md5,job_id,file_repeat_size,repeat_percent) values (?,?,?,?,?,?,?,?,?,?)'
#             sql = 'insert into pcs_directory (id,source_directory,source_directory_md5) values (?,?,?)'
            ins_r=db.execute_update(sql, dirs_list)
            if ins_r:
                print('插入 pcs_directory 表数据成功:')
            else:
                print('插入 pcs_directory 表 数据失败')
        except Exception as e:
            print('pcs_directory 数据库数据失败：',e)
        db.close() 
        
    def delete_pcs_directory_for_dirsid(self,dirs_id):
        '''
        根据dirs_id 的值去删除pcs_directory 表中的数据
        '''
        db = dbaccess.DBAccess()
        try:
            sql = "DELETE FROM pcs_directory where  id='"+dirs_id+"'"
            ob = []
            ins_r=db.execute_delete(sql, ob)
            if ins_r:
                print('按照ID 条件 删除 pcs_directory 表数据成功:')
            else:
                print('按照ID 条件 删除 pcs_directory 表数据失败')
        except Exception as e:
            print('按照ID 条件 删除 pcs_directory 表数据失败：',e)
        db.close()
        
    def insert_pcs_job(self,job_list):
        '''
        向pcs_log数据库插入数据。
        '''
        db = dbaccess.DBAccess()
        try:
            sql = 'insert into pcs_job (id,source_directory,source_directory_md5) values (?,?,?)'
            ins_r=db.execute_update(sql, job_list)
            if ins_r:
                print('插入 pcs_job 表数据成功:')
            else:
                print('插入 pcs_job 表 数据失败')
        except Exception as e:
            print('pcs_job 数据库数据失败：',e)
        db.close()
                
    def get_repeat_photo(self,job_id):
        '''
        从DB中将所有重复的照片信息查询出来
        :return flag
        '''
        db = dbaccess.DBAccess()
        repeatPhotoList = []
        try:
            query_sql = '''
            SELECT file_md5,
                   source_name,
                   image_datetime
            FROM PCS_PHOTO_CLASS
            WHERE JOB_ID = ? AND
                    file_md5 IN (
                        SELECT file_md5
                        FROM PCS_PHOTO_CLASS
                        WHERE JOB_ID = ?
                        GROUP BY file_md5
                        HAVING count(file_md5) > 1
                    )
            ORDER BY file_md5;
            '''
            ob = []
            ob.append(job_id)
            ob.append(job_id)
#             print(ob)
            res = db.execute_query(query_sql,ob)
            rest = []
            for rest in res:
                repeatPhotoList.append(rest)
                         
        except Exception as e:
            print("查询数据库中 指定的表是否存在错误:",e)
        db.close() 
        return repeatPhotoList
    
    def get_repeat_photo_for_directory_num(self,job_id,dirs_id):
        '''
        从DB中将所有重复的照片信息查询出来
        :return flag
        '''
        db = dbaccess.DBAccess()
        try:
            query_sql = '''
            SELECT count(id)
            FROM PCS_PHOTO_CLASS
            WHERE JOB_ID = ? AND
                    DIRS_ID =? AND
                    file_md5 IN (
                        SELECT file_md5
                        FROM PCS_PHOTO_CLASS
                        WHERE JOB_ID = ? AND
                        DIRS_ID = ?
                        GROUP BY file_md5
                        HAVING count(file_md5) > 1
                    )
            '''
            ob = []
            ob.append(job_id)
            ob.append(dirs_id)
            ob.append(job_id)
            ob.append(dirs_id)
#             print(ob)
            res = db.execute_query(query_sql,ob)
            result =res.fetchall()
            count = result[0][0]   
        
        except Exception as e:
            print("查询数据库中 指定的表是否存在错误:",e)
        db.close() 
        return count
    
    
    def get_repeat_photo_to_csv(self):
        '''
        从DB中将所有重复的照片信息查询出来
        :return flag
        '''
        db = dbaccess.DBAccess()
        repeatPhotoList = []
        try:
            f = open('pcs.csv','w')
            query_sql = '''
            SELECT file_md5,
                   source_name,
                   image_datetime
            FROM PHOTO_CLASS
            WHERE file_md5 IN (
                        SELECT file_md5
                        FROM PHOTO_CLASS
                        GROUP BY file_md5
                        HAVING count(file_md5) > 1
                    )
            ORDER BY file_md5;
            '''
            ob = []
            res = db.execute_query(query_sql,ob)
            rest = []
            for rest in res:
                rInfo =''
                for ri in rest:
                    s = ri+','
                    rInfo = rInfo+s
                rInfo = rInfo+'\n'
                f.writelines(rInfo)
            f.close() 
                     
#                 repeatPhotoList.append(rest)
                         
        except Exception as e:
            print("查询数据库中 指定的表是否存在错误:",e)
        db.close() 
        
    def update_pcs_directory_to_repeat(self,tList):
        '''
        从DB中将所有重复的照片信息查询出来
        :return flag
        '''
        db = dbaccess.DBAccess()
        try:
            ob = []
            ob.append(tList)
            update_sql = "UPDATE pcs_directory SET file_repeat_size = ? ,repeat_percent = ? WHERE job_id = ? AND id = ?"           
            res = db.execute_update(update_sql, ob)
            if res:
                print('更新  pcs_directory 表中重复率数据成功:')
            else:
                print('更新  pcs_directory 表 重复率数据失败')
                         
        except Exception as e:
            print("更新  pcs_directory 表 重复率数据错误:",e)
        db.close() 
        
    def update_pcs_directory_deleted_for_id(self,dirs_id):
        '''
        从DB中将所有重复的照片信息查询出来
        :return flag
        '''
        db = dbaccess.DBAccess()
        try:
            ob = []
            dirs_tuple = (dirs_id,)
            ob.append(dirs_tuple)
            print(ob)
            update_sql = "UPDATE pcs_directory SET deleted = 1  WHERE id= ?" 
            print('update_sql=',update_sql)      
            res = db.execute_update(update_sql, ob)
            if res:
                print('更新  pcs_directory 表中删除标识成功:')
            else:
                print('更新  pcs_directory 表 删除标识失败')
                         
        except Exception as e:
            print("更新  pcs_directory 表 删除标识错误:",e)
        db.close() 
    
    def update_pcs_directory_jobid_for_id(self,job_id,dirs_id):
        '''
        根据pcs_directory 表中的ID 值 修改job_id
        :return flag
        '''
        db = dbaccess.DBAccess()
        try:
            ob = []
            dirs_tuple = (job_id,dirs_id)
            ob.append(dirs_tuple)
            update_sql = "UPDATE pcs_directory SET job_id = ?  WHERE id= ?"    
            res = db.execute_update(update_sql, ob)
            if res:
                print('更新  pcs_directory 表中 JOB_ID 成功:')
            else:
                print('更新  pcs_directory 表中 JOB_ID 失败')
                         
        except Exception as e:
            print("更新  pcs_directory 表中 JOB_ID 错误:",e)
        db.close()
            
    def update_pcs_directory_log_to_repeat(self,tList):
        '''
        从DB中将所有重复的照片信息查询出来
        :return flag
        '''
        db = dbaccess.DBAccess()
        try:
            ob = []
            ob.append(tList)
            update_sql = "UPDATE pcs_directory SET file_repeat_size = ? ,repeat_percent = ? WHERE job_id = ? AND id = ?"  
            res = db.execute_update(update_sql,ob)
            if res:
                print('更新 pcs_directory_log 表中重复率数据成功:')
            else:
                print('更新 pcs_directory_log 表 重复率数据失败')
                         
        except Exception as e:
            print("更新 pcs_directory_log 表 重复率数据错误:",e)
        db.close() 
        
    def get_photo_directory_count(self,strdata):
        '''
        判断数据库中 指定的表中，指定条件的数据是否存在。
        :return count
        '''
        db = dbaccess.DBAccess()
        try:
            query_sql = "select count(*) from pcs_directory where source_directory_md5 ='"+strdata+"'"
            ob = []
            res = db.execute_query(query_sql,ob)
            result =res.fetchall()
            count = result[0][0]      
        except Exception as e:
            print("查询数据库中 指定的表中，指定条件的数据是否存在错误:",e)
        db.close() 
        return count
    
    def get_photo_directory_extension_info(self,strdata):
        '''
        判断数据库中 指定的表中，指定条件的数据是否存在。
        :return count
        '''
        db = dbaccess.DBAccess()
        try:
            query_sql = "select id,file_extension_num,file_extension_size from pcs_directory where source_directory_md5 ='"+strdata+"'"
            ob = []
            res = db.execute_query(query_sql,ob)
            directory_result =res.fetchone()
#             print('directory_result=',directory_result)
            result_dict = {}
            result_dict['dirs_id'] = directory_result[0]
            result_dict['file_extension_num'] = directory_result[1]
            result_dict['file_extension_size'] = directory_result[2]         
        except Exception as e:
            print("查询数据库中 指定的表中，指定条件的数据是否存在错误:",e)
        db.close() 
        return result_dict   
    
    def get_pcs_directory_info(self,sdmd5):
        '''
        判断数据库中 指定的表中，指定条件的数据是否存在。
        :return count
        '''
        db = dbaccess.DBAccess()
        try:
            result_dict = {}
            query_sql = "SELECT directory_num,file_num,file_extension_num,file_extension_size,id,source_directory,source_directory_md5,job_id,file_repeat_size,repeat_percent from pcs_directory where source_directory_md5 = ? and deleted = 0"
            ob = []
            ob.append(sdmd5)
            res = db.execute_query(query_sql,ob)
            directory_result =res.fetchone()
            if directory_result != None:   
                          
                result_dict['directory_num'] = directory_result[0]
                result_dict['file_num'] = directory_result[1]
                result_dict['file_extension_num'] = directory_result[2]
                result_dict['file_extension_size'] = directory_result[3]
                result_dict['dirs_id'] = directory_result[4]
                result_dict['source_directory'] = directory_result[5] 
                result_dict['source_directory_md5'] = directory_result[6]
                result_dict['job_id'] = directory_result[7]
                result_dict['file_repeat_size'] = directory_result[8] 
                result_dict['repeat_percent'] = directory_result[9]     
        except Exception as e:
            print("查询pcs_directory 表中，指定条件的数据是否存在错误:",e)
        db.close() 
        return result_dict  
    
    def update_pcs_photo_class_jobid_for_dirsid(self,job_id,dirs_id):
        '''
        根据pcs_phtos_class 表中的dirs_id 值 修改job_id值
        :return flag
        '''
        db = dbaccess.DBAccess()
        try:
            ob = []
            dirs_tuple = (job_id,dirs_id)
            ob.append(dirs_tuple)
            update_sql = "UPDATE pcs_photo_class SET job_id = ?  WHERE dirs_id= ?"      
            res = db.execute_update(update_sql, ob)
            if res:
                print('更新  pcs_directory 表中 JOB_ID 成功')
            else:
                print('更新  pcs_directory 表中 JOB_ID 失败')
                         
        except Exception as e:
            print("更新  pcs_directory 表中 JOB_ID 错误:",e)
        db.close()   
    
    def delete_pcs_photo_class_for_dirsid(self,dirs_id):
        '''
        根据dirs_id 的值去删除 pcs_photo_class 表中的数据
        '''
        db = dbaccess.DBAccess()
        try:
            sql = "DELETE FROM pcs_photo_class where  dirs_id='"+dirs_id+"'"
            ob = []
            ins_r=db.execute_delete(sql, ob)
            if ins_r:
                print('按照 dirs_id 条件 删除  pcs_photo_class 表数据成功:')
            else:
                print('按照  dirs_id 条件 删除  pcs_photo_class 表数据失败')
        except Exception as e:
            print('按照 dirs_id 条件 删除  pcs_photo_class 表数据失败：',e)
        db.close()   